package dao.task;

import java.sql.*;
import java.text.*;
import java.util.ArrayList;
import java.util.List;

/*
3：写好StudentDao，实现必要的CRUD功能（包括分页）
 */
public class StudentDaoImpl extends DbUtil_1 implements StudentDao {

    @Override
    public int add(Student st) {
        String sql = "insert into s_student values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Object[] s = {st.getStuno(), st.getSname(), st.getSex(), st.getTelephone(), st.getQq(), st.getWechat(), st.getGradeid(), new Date(st.getEnterdate().getTime()), st.getFromcity(), new Date(st.getBirthday().getTime()), st.getAddress(), st.getPass(), "DEFAULT"};
        return this.executeSql(sql, s);
    }

    @Override
    public int update(Student st) {
        String sql = "update s_student set sname=?,sex=?,telephone=? where stuno=?";
        Object[] s = {st.getSname(), st.getSex(), st.getTelephone(), st.getStuno()};
        return this.executeSql(sql, s);
    }

    @Override
    public int del(int stuno) {
        String sql = "delete from s_student where stuno=?";
        return this.executeSql(sql, stuno);
    }

    @Override
    public int del(Student st) {
        String sql = "delete from s_student where stuno=?";
        return this.executeSql(sql, st.getStuno());
    }

    @Override
    public Student get(int stuno) {
        Connection con = this.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student st = null;
        try {
            ps = con.prepareStatement("select * from s_student where stuno=?");
            ps.setInt(1, stuno);
            rs = ps.executeQuery();
            if (rs.next()) {
                st = new Student();
                st.setStuno(rs.getInt(1));
                st.setSname(rs.getString(2));
                st.setSex(rs.getString(3));
                st.setTelephone(rs.getString(4));
                st.setQq(rs.getString(5));
                st.setWechat(rs.getString(6));
                st.setGradeid(rs.getInt(7));
                st.setEnterdate(rs.getDate(8));
                st.setFromcity(rs.getString(9));
                st.setBirthday(rs.getDate(10));
                st.setAddress(rs.getString(11));
                st.setPass(rs.getString(12));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.close(rs);
            this.close(ps);
            this.close(con);
        }
        return st;
    }


    @Override
    public List<Student> findALL() {
        List<Student> list = new ArrayList<>();
        Connection con = this.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement("select * from s_student");
            rs = ps.executeQuery();
            while (rs.next()) {
                Student st = new Student();
                st.setStuno(rs.getInt(1));
                st.setSname(rs.getString(2));
                st.setSex(rs.getString(3));
                st.setTelephone(rs.getString(4));
                st.setQq(rs.getString(5));
                st.setWechat(rs.getString(6));
                st.setGradeid(rs.getInt(7));
                st.setEnterdate(rs.getDate(8));
                st.setFromcity(rs.getString(9));
                st.setBirthday(rs.getDate(10));
                st.setAddress(rs.getString(11));
                st.setPass(rs.getString(12));
                list.add(st);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.close(rs);
            this.close(ps);
            this.close(con);
        }
        return list;
    }


    @Override
    public List<Student> paging(int pageNo, int pageSize) {
        List<Student> list = new ArrayList<>();
        Connection con = this.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement("select * from s_student limit ?,?");
            ps.setInt(1, (pageNo - 1) * pageSize);
            ps.setInt(2, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student st = new Student();
                st.setStuno(rs.getInt(1));
                st.setSname(rs.getString(2));
                st.setSex(rs.getString(3));
                st.setTelephone(rs.getString(4));
                st.setQq(rs.getString(5));
                st.setWechat(rs.getString(6));
                st.setGradeid(rs.getInt(7));
                st.setEnterdate(rs.getDate(8));
                st.setFromcity(rs.getString(9));
                st.setBirthday(rs.getDate(10));
                st.setAddress(rs.getString(11));
                st.setPass(rs.getString(12));
                list.add(st);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.close(rs);
            this.close(ps);
            this.close(con);
        }
        return list;
    }


    @Override
    public List<Student> find(String sname, int pageNo, int pageSize) { //模糊查询
        List<Student> list = new ArrayList<>();
        Connection con = this.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement("select * from s_student where sname like ? limit ?,?");
            //!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
            ps.setString(1, "%" + sname + "%");
            ps.setInt(2, (pageNo - 1) * pageSize);
            ps.setInt(3, pageSize);
            rs = ps.executeQuery();
            while (rs.next()) {
                Student st = new Student();
                st.setStuno(rs.getInt(1));
                st.setSname(rs.getString(2));
                st.setSex(rs.getString(3));
                st.setTelephone(rs.getString(4));
                st.setQq(rs.getString(5));
                st.setWechat(rs.getString(6));
                st.setGradeid(rs.getInt(7));
                st.setEnterdate(rs.getDate(8));
                st.setFromcity(rs.getString(9));
                st.setBirthday(rs.getDate(10));
                st.setAddress(rs.getString(11));
                st.setPass(rs.getString(12));
                list.add(st);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.close(rs);
            this.close(ps);
            this.close(con);
        }
        return list;
    }
}
